<?php

defined('_JEXEC') or die;

class HelperBG {

    /**
     * 指定する日時のYYYY年MM月DD日の表示を取得する。
     *
     * @param  $date  datetime 指定する日時（yyyy-mm-dd hh:mm:ssフォーマット）
     * @return YYYY年MM月DD日
     */
	function F_GetDate_JP($date){
        if ($date != null && $$date != '0000-00-00 00:00:00' ) {
            $d      = getdate(strtotime($date));
            $dateJP = $d['year']."年".$d['mon']."月".$d['mday']."日";
        }else{
            $dateJP = '';
        }
        return $dateJP;
    }

    /*Check URL in string*/
    function txt2link($text){
        $text = ereg_replace( "www\.", "http://www.", $text );
        $text = ereg_replace( "www2\.", "https://www2.", $text );
        $text = ereg_replace( "http://http://www\.", "http://www.", $text );
        $text = ereg_replace( "https://http://www\.", "https://www.", $text );
        $reg_exUrl = "/(http|https|ftp|ftps)\:\/\/[a-zA-Z0-9\-\.]+\.[a-zA-Z]{2,4}(\/\S*)?/";
        if(preg_match($reg_exUrl, $text, $url)) {
            $text = preg_replace($reg_exUrl, '<a href="'.$url[0].'" rel="nofollow">'.$url[0].'</a>', $text);
        }
        return $text;
    }

    /*Check file size*/
    public function filesize_formatted($path)
    {
        $size = filesize($path);
        $units = array( 'B', 'KB', 'MB', 'GB', 'TB', 'PB', 'EB', 'ZB', 'YB');
        $power = $size > 0 ? floor(log($size, 1024)) : 0;
        return number_format($size / pow(1024, $power), 2, '.', ',') . ' ' . $units[$power];
    }

    function getUploadConfig(){
        return array(
            "size"=>"26214400",
            "type"=>"rar,zip,csv,pdf,doc,docx,xlsx,xls,pptx,ppt,txt,jpg,JPG,JPEG,PNG,GIF,jpeg,png,gif,mp3,avi,mp4,exe,swf,wav,wmv,avi,asf,mpeg,mpg,mpe,mov,3gp,m4a,aac,m3u,dotx,ppsx,potx,xltx"
        );
    }

    function getIDAuto($tableName, $column, $lengh = null){
        $sqlString = "SELECT $column FROM $tableName ORDER BY cast($column as unsigned) DESC LIMIT 0,1";
        $db = JFactory::getDbo();

        $db->setQuery($sqlString);
        $id = (int)$db->loadResult();
        if($lengh){
            $id = self::genertatorID($id, $lengh);
        }else{
            $id ++;
        }
        return $id;
    }

    public function genertatorID($val, $lengh){
        $lengh = $lengh - strlen($val);
        $val++;
    	for($i=0; $i<$lengh; $i++){
    	   $val = '0'.$val;
    	}

        return $val;
    }

    /**
     * HelperBG::getFacility()
     *  'all' => '全施設', - t?t c? c?c thi?t b?
     *  'all_gym' => '体育館全て', - t?t c? c?c ph?ng t?p th? d?c
     *  'gym1' => '第一体育館', - ph?ng t?p th? d?c 1
     *  'gym2' => '第二体育館', - ph?ng t?p th? d?c 2
     *  'pool' => 'プール', - h? b?i
     *  'dock' => '艇庫'  - nh? ch?a thuy?n
     * @return
     */
    public function getFacility(){
        return array(
            'all' => '全施設',
            'all_gym' => '体育館全て',
            'gym1' => '第一体育館',
            'gym2' => '第二体育館',
            'pool' => 'プール',
            'dock' => '艇庫'
        );
    }

    public function getMonthOption($selected = null, $select_curent_month = false){

        $option_month ="";
        if(empty($selected) && $select_curent_month){
            $selected = date('m');
        }
        for ($i=1 ; $i<=12; $i++){
            $val = str_pad($i,2,'0',STR_PAD_LEFT);
            $isSelect = $val == $selected ? "selected" : "";
            $option_month.= "<option $isSelect value='$val'>$val</option>\n";
        }

        return $option_month;
    }

    public function getYearOption($selected = null, $select_current_year = false){

        $option_year = "";
        if(empty($selected) && $select_current_year){
            $selected = date('Y');
        }

        for ( $i = 1998; $i <= date('Y')+1; $i++ ){
            $isSelect = $i == $selected ? "selected" : "";
            $option_year.= "<option $isSelect value='$i'>$i</option>\n";
        }

        return $option_year;
    }


    /**
     * HelperBG::getMAMA_TYPE()
     * 1 - 通常メールマガジン - sent now
     * 2 - すぐに送信 - sent schedule
     * 3 - 指導員更新用 - sent coach
     * @return
     */
    public function getMAMA_TYPE(){
    	return array(
            1=>'通常メールマガジン',
            2=>'すぐに送信',
            3=>'指導員更新用'
        );
    }



    public function getDateCombobox(){
    	$listYear = array();
        $listMonth = array();
        $listDay = array();
        for ($i=2002; $i < 20; $i++)
        {
            array_push($listYear,$i);
        }
        for ($i=1; $i < 13; $i++)
        {
            array_push($listMonth,$i);
        }
        for ($i=1; $i < 32; $i++)
        {
            array_push($listDay,$i);
        }
        return array(
            'year'      => $listYear,
            'month'     => $listMonth,
            'day'       => $listDay
        );
    }

    /**
     * HelperBG::sendMail()
     * SEND MAIL
     * @param mixed $Receiver object or array($obEmailName=>'email', $obFullName=>'name')
     * @param mixed $Subject
     * @param mixed $Body
     * @param mixed $obEmailName
     * @param mixed $obFullName
     * @param mixex $rejectLink
     * @return void
     */
     public function sendMail($Receiver, $Subject, $Body, $obEmailName= null, $obFullName = null, $rejectLink = ''){

        $mailer = JFactory::getMailer();
        $config = JFactory::getConfig();

        $sender = array(
            $config->getValue( 'config.mailfrom' ),
            $config->getValue( 'config.fromname' ) );
        $mailer->isHTML(true);
        $mailer->Encoding = 'base64';
        $mailer->setSender($sender);
        //dump($mailer);
        $mailer->setSubject($Subject);
        $mailer->setBody($Body.$rejectLink);

        // sent to list
        if(is_array($Receiver) || is_object($Receiver)){
            foreach($Receiver as $re){
                $re = (object)$re;
                // neu nhu email la null thi khong lam gi ca
                if(!$re->$obEmailName){
                    continue;
                }
                // insert link reject, code here will speed excute
                if($rejectLink){
                    $link = JURI::base()."unsendmail.php?mail=unsendmail&email=".md5($re->$obEmailName);
                    $link = "<a href='$link'>$link</a>";
                    $rejectLink =   "<p>*****************************</p>
                                        <p style='height:400px'>
                                        メルマガの配信停止はこちら<br />
                                       $link</p>";
                    $mailer->setBody($Body.$rejectLink);
                }
                $mailer->ClearAllRecipients();
                $mailer->addRecipient($re->$obEmailName, $re->$obFullName);
                //die;
                $send = $mailer->Send();
            }
        }else{ // send one user - format email David Tran<hoang@gmail.com>, Tran<tran@gmail.com>
            // get fullname and email
            $Receiver = explode(';', $Receiver);

            foreach($Receiver as $re){

                if($re){
                    // get name , email
                    $e_name = substr($re, 0, strripos($re,'(', 1));
                    $e_mail = substr($re, strripos($re,'(', 1));
                    $re = array();
                    $re[0] = $e_name;
                    $re[1] = str_replace(')', '', $e_mail);
                    $re[1] = str_replace('(', '', $re[1]);

                    if(!$re[1]){
                        $re[1] = str_replace(' ',null,$re[0]);
                    }else{
                        // delete char '>' end
                        $re[1] = str_replace(' ',null,substr($re[1], 0, strlen($re[1])-1));
                    }
                    // insert link reject, code here will speed excute
                    if($rejectLink){
                        $link = JURI::base()."unsendmail.php?mail=unsendmail&email=".md5($re[1]);
                        $link = "<a href='$link'>$link</a>";
                         $rejectLink =   "<p>*****************************</p>
                                            <p style='height:400px'>
                                            メルマガの配信停止はこちら<br />
                                            $link</p>";
                         $mailer->setBody($Body.$rejectLink);
                    }

                    $mailer->ClearAllRecipients();
                    $mailer->addRecipient($re[1], $re[0]);

                    $send = $mailer->Send();
                }
            }
        }

    }

    /**
	* Read file CSV return array
    * @param file name
    * @return array
	*/
    public function readCSV($file){
        ini_set('memory_limit', '1000M');
        ini_set('auto_detect_line_endings',TRUE); //Check keyword /n or /r in MAC OS and WINDOWS, LINUX
        if (fopen($file, 'r')) {
            $file_handle = fopen($file, 'r');
            setlocale(LC_ALL, 'Ja_JP.Shift_JIS');
            while (!feof($file_handle)) {
                $line = fgetcsv($file_handle, 1024);//Hung bo => nho mo comment lai
                //$line = fgetcsv($file_handle, 1024,','); //input data
                $line = str_replace("\"","",$line);

                if(count($line) > 1){ //Hung bo => nho mo comment lai
                   $line_of_text[] = $line; //Hung bo => nho mo comment lai
                }else{ //Hung bo => nho mo comment lai
                    $line = explode(';', $line[0]);//Hung bo => nho mo comment lai
                    $line_of_text[] = $line; //input data
               } //Hung bo => nho mo comment lai
            }
            $data = "";
            foreach ($line_of_text as $key => $value) {
                if (count($value) == 1) {
                    unset($line_of_text[$key]);
                    continue;
                }
                foreach($value as $k=>$v){
                    $data[$key][$k] = iconv ("SJIS", "UTF-8" , $v ); //SJIS => UTF-8 before insert Database
                }
            }
            return $data;
        }
    }
    /*---------------------------------------*/
    /**
    * Export data from mysql (table SERQ) to file csv
    * Author: Hung
    */
    public function export_csv_questionare($id){
        $is_bang = $id;
        ini_set('memory_limit', '1000M');
        //Get thong tin thong tin file CSV
        $qr = "SELECT username,QUES_PAGQ_CODE,RESQ_QUES_CODE,RESQ_VALUE,QUES_TYPE
                FROM sess
                INNER JOIN resq ON SESS_CODE = RESQ_SESS_CODE
                INNER JOIN ques ON RESQ_QUES_CODE = QUES_CODE
                INNER JOIN kc2vr_users ON SESS_USER_CODE = id
                WHERE SESS_SERQ_CODE = $id
                ORDER BY RESQ_QUES_CODE";

        $db = JFactory::getDbo();
        $db->setQuery($qr);
        $result = $db->loadObjectList();
        if ($result != null){
            $arr_user = array();
            foreach($result as $k => $value){
                $username       = $value->username;
                $QUES_PAGQ_CODE = $value->QUES_PAGQ_CODE;
                $RESQ_QUES_CODE = $value->RESQ_QUES_CODE;
                $RESQ_VALUE     = $value->RESQ_VALUE;

                if($value->QUES_TYPE == 1 || $value->QUES_TYPE == 2){
                    //Get cau tra loi cua user
                    $qr = "SELECT RESQ_VALUE
                            FROM sess
                            INNER JOIN resq ON SESS_CODE = RESQ_SESS_CODE
                            INNER JOIN kc2vr_users ON SESS_USER_CODE = id
                            WHERE username = '".$value->username."' AND RESQ_QUES_CODE = ".$value->RESQ_QUES_CODE;
                    $db->setQuery($qr);
                    $arr = $db->loadObjectList();
                    foreach($arr as $v){
                        $arr_test[] = $v->RESQ_VALUE;
                    }

                    //Get cau tra loi cua cau hoi tuong ung
                    $qr = "SELECT ANSQ_CODE
                            FROM ansq
                            WHERE ANSQ_QUES_CODE = $value->RESQ_QUES_CODE
                            ORDER BY ANSQ_CODE ASC";
                    $db->setQuery($qr);
                    $arr1 = $db->loadObjectList();
                    foreach($arr1 as $v){
                        $arr_test1[] = $v->ANSQ_CODE;
                    }
                    //Get index cua cau tra loi
                    foreach ($arr_test as $v) {
                        $temp[] = array_search($v, $arr_test1) + 1;
                    }
                    //Tao mang nhieu chieu chua cau tra loi cua 1 user
                    $arr_user[$username][$QUES_PAGQ_CODE][$RESQ_QUES_CODE][] = implode(",", $temp);

                    //Reset array truoc khi lap lai
                    $arr_test = null;
                    $arr_test1 = null;
                    $temp = null;
                }
                else{
                    $arr_user[$username][$QUES_PAGQ_CODE][$RESQ_QUES_CODE][] = $RESQ_VALUE;
                }
            }





            $max_question = 0;
            //sort($arr_user);
            foreach ($arr_user as $id => $page) {

                foreach ($page as $id_page => $question) {

                    $qr2 = "SELECT DISTINCT `QUES_CODE` FROM ques
                        LEFT JOIN resq ON `QUES_CODE` = `RESQ_QUES_CODE`
                        WHERE `QUES_SERQ_CODE` = $is_bang AND QUES_PAGQ_CODE = $id_page
                        ORDER BY RESQ_QUES_CODE";
                      //  echo $qr2;
                    $db->setQuery($qr2);
                    $questions = $db->loadAssocList();



                    foreach($questions as $que){
                        $que = $que['QUES_CODE'];

                        if(!isset($question[$que])){
                            $question[$que] = array();
                        }
                    }

                    ksort($question);

                    $num = 0;
                    $str =array();
                    foreach ($question as $ansers) {
                        $str[] = $ansers[0]; //Vi gia tri cua cac pt trong mang trung nen lay 1 pt dau la du
                        $num ++;
                    }
                    if($num > $max_question){ //Kiem tra so cau hoi trong moi page
                        $max_question = $num; //Tim duoc so cau hoi nhieu nhat trong 1 page
                    }
                    //Dap an cua cac cau hoi duoc cach nhau boi dau phay
                    $str = implode('","', $str); //Dau cham phay
                    $data .= "\"$id\",\"$id_page\",\"$str\"\r\n"; //Dau cham phay
                }
            }

            $csv_output = "UserID,PageID,"; //Dau cham phay
            for ($i=1;$i<=$max_question;$i++) {
                if($i== $max_question){
                    $csv_output  .= "Quest_$i";
                }
                else{
                    $csv_output  .= "Quest_$i,"; //Dau cham phay
                }
            }
            $csv_output .= "\r\n";
            $csv_output .= $data;
            $csv_output  = mb_convert_encoding($csv_output,'Shift-JIS','UTF-8');
            //print_r($csv_output);die;
        }
        $filename = "quest_".date("Y-m-d_H-i",time());
// die;
        header("Content-Encoding: Shift-JIS");
        header("Content-Type: text/csv; charset= Shift-JIS");
        header("Content-disposition: csv" . date("Y-m-d") . ".csv");
        header("Content-disposition:attachment; filename=" . $filename . ".csv");
        print_r($csv_output);
        exit;
    }
    /*--------------------------------------*/

    /**
	* Export data from mysql (table SERQ) to file csv
    * Author: Hung
	*/
   /*public function export_csv_questionare($id){
        ini_set('memory_limit', '1000M');
        //Get lay Ma cau hoi
        $qr = "SELECT DISTINCT RESQ_QUES_CODE
                FROM sess
                LEFT JOIN resq ON SESS_CODE = RESQ_SESS_CODE
                LEFT JOIN ques ON RESQ_QUES_CODE = QUES_CODE
                WHERE SESS_SERQ_CODE = $id";

        $db = JFactory::getDbo();
		$db->setQuery($qr);
        $arr_quest_list = $db->loadObjectList();
        $csv_output  = "UserID,PageID,";
        sort($arr_quest_list);
        foreach ($arr_quest_list as $key=>$v) {
            if (count($arr_quest_list) == $key+1){
                $csv_output  .= "\"Quest_$v->RESQ_QUES_CODE\"";
            } else {
                $csv_output  .= "\"Quest_$v->RESQ_QUES_CODE\",";
            }
        }
        $csv_output  = mb_convert_encoding($csv_output,'Shift-JIS','UTF-8');
        $header = explode(",", $csv_output);

        //Set lai Ma cau hoi bat dau tu cau mot
        $qr = "SELECT COUNT(DISTINCT RESQ_QUES_CODE)
                FROM sess
                LEFT JOIN resq ON SESS_CODE = RESQ_SESS_CODE
                LEFT JOIN ques ON RESQ_QUES_CODE = QUES_CODE
                WHERE SESS_SERQ_CODE = $id";

        $db = JFactory::getDbo();
		$db->setQuery($qr);
        $number = $db->loadResult();
        $csv_output  = "UserID,PageID,";
        for ($i=1;$i<=$number;$i++) {
            if($i== $number){
                $csv_output  .= "\"Quest_$i\"";
            }
            else{
                $csv_output  .= "\"Quest_$i\",";
            }
        }
        $csv_output  = mb_convert_encoding($csv_output,'Shift-JIS','UTF-8');

        $csv_output .= "\n";

        //Get thong tin thong tin file CSV
        $qr = "SELECT username,QUES_PAGQ_CODE,RESQ_QUES_CODE,RESQ_VALUE,QUES_TYPE
                FROM sess
                LEFT JOIN resq ON SESS_CODE = RESQ_SESS_CODE
                LEFT JOIN ques ON RESQ_QUES_CODE = QUES_CODE
                LEFT JOIN kc2vr_users ON SESS_USER_CODE = id
                WHERE SESS_SERQ_CODE = $id
                ORDER BY QUES_PAGQ_CODE";

        $db = JFactory::getDbo();
		$db->setQuery($qr);
        $result = $db->loadObjectList();

        if ($result != null){
            $i=0;
            foreach($result as $value){
                if($value->QUES_TYPE == 1 || $value->QUES_TYPE == 2){
                    //Get cau tra loi cua user
                    $qr = "SELECT RESQ_VALUE
                            FROM sess
                            LEFT JOIN resq ON SESS_CODE = RESQ_SESS_CODE
                            LEFT JOIN kc2vr_users ON SESS_USER_CODE = id
                            WHERE username = '".$value->username."' AND RESQ_QUES_CODE = ".$value->RESQ_QUES_CODE;
                    $db->setQuery($qr);
                    $arr = $db->loadObjectList();
                    foreach($arr as $v){
                        $arr_test[] = $v->RESQ_VALUE;
                    }
                    //Get cau tra loi cua cau hoi tuong ung
                    $qr = "SELECT ANSQ_CODE
                            FROM ansq
                            WHERE ANSQ_QUES_CODE = $value->RESQ_QUES_CODE
                            ORDER BY ANSQ_CODE";
                    $db->setQuery($qr);
                    $arr1 = $db->loadObjectList();
                    foreach($arr1 as $v){
                        $arr_test1[] = $v->ANSQ_CODE;
                    }
                    //Get index cua cau tra loi
                    foreach ($arr_test as $v) {
                        $temp[] = array_search($v, $arr_test1) + 1;
                    }
                    //input vao lai $value->RESQ_VALUE
                    $value->RESQ_VALUE = implode(",", $temp);
                    //Reset array truoc khi lap lai
                    $arr_test = null;
                    $arr_test1 = null;
                    $temp = null;
                }
            }
            $result = array_map("unserialize", array_unique(array_map("serialize", $result))); //Loi bo Oject trung trong array
            foreach ($result as $item) {
            	foreach ($item as $key => $value) {
        	       if($key != "QUES_TYPE"){
            	       if($key == "RESQ_QUES_CODE"){
            	           $find = "\"Quest_" . $value . "\"";
            	           $index = array_search($find, $header) - 2;
                           for($i = 0; $i < $index ; $i++){
                                $csv_output .= ",";
                           }
                           continue;
            	       }

            	       if($key == "RESQ_VALUE"){
        	               $csv_output .= "\"".mb_convert_encoding($value,'Shift-JIS','UTF-8')."\""; //Todo: Chuy?n d? li?u ki?u UTF-8 => Shift-JIS ?? xu?t file csv d?ng Shift-JIS
                	   }else{
    		               $csv_output .= "\"".mb_convert_encoding($value,'Shift-JIS','UTF-8')."\",";
                        }
                    }
        	   }
               $csv_output .= "\r\n";
            }
        }

        $filename = "quest_".date("Y-m-d_H-i",time());

        header("Content-Encoding: Shift-JIS");
        header("Content-Type: text/csv; charset= Shift-JIS");
        header("Content-disposition: csv" . date("Y-m-d") . ".csv");
        header("Content-disposition:attachment; filename=" . $filename . ".csv");
        print_r($csv_output);
        exit;
    }*/

    /**
	* Export data from mysql (table TOWN) to file csv
    * Author: Hung
	*/
    public function export_csv(){
        ini_set('memory_limit', '1000M');
        //Todo: Ti?u ?? d? li?u: hi?n th? d?ng ??u ti?n trong file CSV
        $csv_output  = "市町村コード,県コード,市町村名,市町村名カナ,人口,世帯数,面積,密度,郵便番号,住所1,住所2,TEL,FAX,最寄駅,メールアドレス,メールアドレス_配信先名,市町村長名,市町村長_就任日,議会議長名,教育長名,合併、境界変更等の状況,シンボル_鳥,シンボル_花,シンボル_木,シンボル_その他,保育園数,幼稚園数,小学校数,中学校数,高校数,国内姉妹・交流都市_市町村名,海外姉妹・姉妹都市_都市名,主要産業,宣言,市町村PR,備考,地図情報URL,URL,教育長就任日（新規）,問い合わせ先,同期済/未同期";
        $csv_output  = mb_convert_encoding($csv_output,'Shift-JIS','UTF-8');
        $csv_output .= "\n";

        $qr = "SELECT TOWN_CODE,pr.PREF_NAME,TOWN_NAME,TOWN_KANA,TOWN_POPU,TOWN_HOUS,TOWN_AREA,TOWN_DENS,TOWN_POST,TOWN_ADD1,TOWN_ADD2,TOWN_TEL,TOWN_FAX,TOWN_STAT,TOWN_MAIL,TOWN_MNAME,TOWN_HNAME,DATE(TOWN_HDATE)
                    ,TOWN_DNAME,TOWN_ENAME,TOWN_UNIF,TOWN_SYM1,TOWN_SYM2,TOWN_SYM3,TOWN_SYM4,TOWN_NURS,TOWN_CHIL,TOWN_ELEM,TOWN_JUNI,TOWN_HIGH
                    ,TOWN_SIS1,TOWN_SIS2,TOWN_INDU,TOWN_DECL,TOWN_PR,TOWN_NOTE,TOWN_MURL,TOWN_URL,DATE(TOWN_EDATE),TOWN_CONT,TOWN_DBSY
				FROM town t
                LEFT JOIN pref pr ON t.TOWN_PREF_CODE = pr.PREF_CODE
                WHERE TOWN_WEBF = 1
                ORDER BY TOWN_CODE DESC";

        $db = JFactory::getDbo();
		$db->setQuery($qr);
        $result = $db->loadObjectList();

        if ($result != null){
            foreach($result as $value){
                /* check TOWN_DBSY */
                if($value->TOWN_DBSY == 1){ $value->TOWN_DBSY = "同期済";}else{ $value->TOWN_DBSY = "未同期"; }
            }
            foreach ($result as $item) {
            	foreach ($item as $key => $value) {
            	   //Todo: Ki?m tra h?t 1 d?ng ?? xu?ng h?ng
            	   if($key == "TOWN_DBSY"){

            	       $csv_output .= "\"".mb_convert_encoding($value,'Shift-JIS','UTF-8')."\""; //Todo: Chuy?n d? li?u ki?u UTF-8 => Shift-JIS ?? xu?t file csv d?ng Shift-JIS
            	   }else{
            		$csv_output .= "\"".mb_convert_encoding($value,'Shift-JIS','UTF-8')."\",";
                    }
            	}
            	$csv_output .= "\r\n";
            }
        }
        $filename = "town_".date("Y-m-d_H-i",time());
        //$filename = "town_";

        header("Content-Encoding: Shift-JIS");
        header("Content-Type: text/csv; charset= Shift-JIS");
        header("Content-disposition: csv" . date("Y-m-d") . ".csv");
        header("Content-disposition:attachment; filename=" . $filename . ".csv");
        print_r($csv_output);
        exit;
    }

    /**
	* Export data from mysql (table OCBA) to file csv
	*/
    public function export_csv_ocba(){
        ini_set('memory_limit', '1000M');
        //Todo: Ti?u ?? d? li?u: hi?n th? d?ng ??u ti?n trong file CSV
        $csv_output  = "センターコード,名称,略称,カナ,期別コード,類型詳細コード,クラブ種別コード,市町村コード,クラブ支援団体名,所在地_郵便番号,所在地_住所1,所在地_住所2,電話番号,FAX番号,連絡先_郵便番号,連絡先_住所1,連絡先_住所2,連絡先_電話番号,連絡先_FAX番号,連絡先名,DM区分,DM_郵便番号,DM_住所1,DM_住所2,DM_宛先,役場_メールアドレス,役場_メール担当者名,教育委員_メールアドレス,教育委員_メール担当者名,担当部署_メールアドレス,担当部署_メール担当者名,センター_メールアドレス,センター_メール担当者名,その他_メールアドレス,その他_メール担当者名,HPアドレス,地図情報URL,所長/代表者名,連絡担当者名,委託先名,開設年月日,譲渡年月日,廃止年月日,休部フラグ,開館/閉館時間,開館期間,休館日,センターPR,画像ファイル名,総合型クラブフラグ,高齢者事業フラグ,カヌーフラグ,ヨットフラグ,学校教育関係事業フラグ,外部団体の受け入れフラグ,その他施設情報,コンパス利用場所コード,コンパス利用場所詳細,同期済/未同期";
        $csv_output  = mb_convert_encoding($csv_output,'Shift-JIS','UTF-8');
        $csv_output .= "\n";

        $qr = "SELECT DISTINCT OCBA_CODE,OCBA_NAME,OCBA_SHOR,OCBA_KANA,TERM_NAME,PEDE_NAME,CLUB_NAME,TOWN_NAME,OCBA_SUPP,OCBA_POST,OCBA_ADD1,OCBA_ADD2,OCBA_TEL,OCBA_FAX,OCBA_LPOST,OCBA_LADD1,OCBA_LADD2,OCBA_LTEL,OCBA_LFAX,OCBA_LNAME,OCBA_DM,OCBA_DPOST,OCBA_DADD1,OCBA_DADD2,OCBA_DNAME,OCBA_YMAIL,OCBA_YNAME,OCBA_EMAIL,OCBA_ENAME,OCBA_TMAIL,OCBA_TNAME,OCBA_CMAIL,OCBA_CNAME,OCBA_OMAIL,OCBA_ONAME,OCBA_URL,OCBA_MURL,OCBA_HEAD,OCBA_CONT,OCBA_ENTR,DATE(OCBA_SDATE),DATE(OCBA_TDATE),DATE(OCBA_EDATE),OCBA_EFLAG,OCBA_STIME,OCBA_PERI,DATE(OCBA_REST),OCBA_PR,OCBA_IMG,OCBA_PUTF,OCBA_LARF,OCBA_CANF,OCBA_YACF,OCBA_SCHF,OCBA_OUTF,OCBA_INST,COMA_NAME,OCBA_CODT,OCBA_DBSY
                    FROM ocba
                    LEFT JOIN term ON OCBA_TERM_CODE = TERM_CODE
                    LEFT JOIN pett ON OCBA_PETT_CODE = PETT_CODE LEFT JOIN pede ON PETT_PEDE_CODE=PEDE_CODE
                    LEFT JOIN club ON OCBA_CLUB_CODE =CLUB_CODE
                    LEFT JOIN town ON OCBA_TOWN_CODE = TOWN_CODE
                    LEFT JOIN coma ON OCBA_COMA_CODE = COMA_CODE
                WHERE OCBA_WEBF = 1
                ORDER BY OCBA_CODE DESC";

        $db = JFactory::getDbo();
		$db->setQuery($qr);
        $result = $db->loadObjectList();
        if ($result != null){
            foreach($result as $value){
                /* check TOWN_DBSY */
                if($value->OCBA_DBSY == 1){ $value->OCBA_DBSY = "同期済";}else{ $value->OCBA_DBSY = "未同期"; }
            }
            foreach ($result as $item) {
            	foreach ($item as $key => $value) {
            	   //Todo: Ki?m tra h?t 1 d?ng ?? xu?ng h?ng
            	   if($key == "OCBA_DBSY"){

            	       $csv_output .= "\"".mb_convert_encoding($value,'Shift-JIS','UTF-8')."\""; //Todo: Chuy?n d? li?u ki?u UTF-8 => Shift-JIS ?? xu?t file csv d?ng Shift-JIS
            	   }else{
            		$csv_output .= "\"".mb_convert_encoding($value,'Shift-JIS','UTF-8')."\",";
                    }
            	}
            	$csv_output .= "\r\n";
            }
        }
        $filename = "ocba_".date("Y-m-d_H-i",time());
        // force download
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");

        header("Content-Encoding: Shift-JIS");
        header("Content-Type: text/csv; charset= Shift-JIS");

        header("Content-disposition: csv" . date("Y-m-d") . ".csv");
        header("Content-disposition:attachment; filename=" . $filename . ".csv");
        print_r($csv_output);
        exit;
    }

    /**
	* Export data from mysql (table ACTT) to file csv
	*/
    public function export_csv_actt($ocba_code = null){
        ini_set('memory_limit', '1000M');
        //Todo: Tiêu đề dữ liệu: hiển thị dòng đầu tiên trong file CSV
        $csv_output  = "イベントID,活動名,募集ステータス,オススメ度,センター．クラブ,開催期間,募集ステータス,会場,主催,後援,協力,問合せ先,趣旨,対象,性別,参加人数,参加費,総事業費,事業内容,備考,施設,対象,内容,画像1,画像1の説明,画像2,画像2の説明,画像3,画像3の説明,画像4,画像4の説明,画像5,画像5の説明,添付資料1,添付ファイル1の説明,添付資料2,添付ファイル2の説明,添付資料3,添付ファイル3の説明,添付資料4,添付ファイル4の説明,添付資料5,添付ファイル5の説明,添付資料6,添付ファイル6の説明";
        $csv_output  = mb_convert_encoding($csv_output,'Shift-JIS','UTF-8');
        $csv_output .= "\n";

        $qr = "SELECT DISTINCT ACTT_CODE,ACTT_NAME,ACTT_RECR,ACTT_RECOMMENDED_DEGREE,OCBA_NAME,ACTT_PERIOD_START,ACTT_PERIOD_FINISH,ACTT_VENUE,ACTT_TOCHUC,ACTT_TAITRO,ACTT_COOPERATION,ACTT_CONTACT,ACTT_EFFECT,ACTT_TARGET,ACTT_SEX,ACTT_NUMBER_OF_PARTICIPANTS,ACTT_ENTRY_FEE,ACTT_TOTAL_PROJECT_COST,ACTT_BUSINESS,ACTT_REMARKS,ACTT_MFAC_CODE_LIST,ACTT_MOBJ_CODE_LIST,ACTT_MCON_CODE_LIST,ACTT_IMAGE_1,ACTT_DECR_IMG_1,ACTT_IMAGE_2,ACTT_DECR_IMG_2,ACTT_IMAGE_3,ACTT_DECR_IMG_3,ACTT_IMAGE_4,ACTT_DECR_IMG_4,ACTT_IMAGE_5,ACTT_DECR_IMG_5,ACTT_ATTACHMENT_1,ACTT_DECR_ATT_1,ACTT_ATTACHMENT_2,ACTT_DECR_ATT_2,ACTT_ATTACHMENT_3,ACTT_DECR_ATT_3,ACTT_ATTACHMENT_4,ACTT_DECR_ATT_4,ACTT_ATTACHMENT_5,ACTT_DECR_ATT_5,ACTT_ATTACHMENT_6,ACTT_DECR_ATT_6,ACTT_DBSY
                    FROM actt
                    	LEFT JOIN ocba ON ACTT_OCBA_CODE = OCBA_CODE
                    	LEFT JOIN town ON OCBA_TOWN_CODE = TOWN_CODE
                    	LEFT JOIN pref ON TOWN_PREF_CODE = PREF_CODE
                     WHERE ACTT_WEBF = 1";
        if(isset($ocba_code)){
            $qr .= " WHERE ACTT_OCBA_CODE = $ocba_code ";
        }
        $qr .= " ORDER BY ACTT_CODE DESC ";
        $db = JFactory::getDbo();
		$db->setQuery($qr);
        $result = $db->loadObjectList();
        //Todo: xu ly gia tri

        foreach($result as $value){
            if($value->ACTT_RECR == 1){
                $value->ACTT_RECR = "募集開始前";
            }
            elseif(($value->ACTT_RECR == 2)){
                $value->ACTT_RECR = "募集中";
            }
            else{
                $value->ACTT_RECR = "募集終了";
            }
            if($value->ACTT_RECOMMENDED_DEGREE == 1){
                $value->ACTT_RECOMMENDED_DEGREE = "★";
            }
            elseif(($value->ACTT_RECOMMENDED_DEGREE == 2)){
                $value->ACTT_RECOMMENDED_DEGREE = "★★";
            }
            else{
                $value->ACTT_RECOMMENDED_DEGREE = "★★★";
            }
            $value->ACTT_PERIOD_START = HelperBG::F_GetDate_JP($value->ACTT_PERIOD_START)."/".HelperBG::F_GetDate_JP($value->ACTT_PERIOD_FINISH);
            /* check TOWN_DBSY */
            if($value->OCBA_DBSY == 1){ $value->OCBA_DBSY = "同期済";}else{ $value->OCBA_DBSY = "未同期"; }
            /* check ACTT_SEX */
            if($value->ACTT_SEX == 0){
                $value->ACTT_SEX = "男性";
            }
            elseif($value->ACTT_SEX == 1){
                $value->ACTT_SEX = "女性";
            }
            else{
                $value->ACTT_SEX = "全て";
            }
            /* process ACTT_MFAC_CODE_LIST */
            $qr = "SELECT MFAC_NAME FROM mfac WHERE MFAC_CODE IN (".$value->ACTT_MFAC_CODE_LIST.")";
            // order by
            $qr .= " ORDER BY MFAC_CODE ASC";
            // load data
            $db->setQuery($qr);
            $mfac = $db->loadObjectList();

            $temp1 = array();
            if($mfac != null){
                foreach($mfac as $item1){
                    $temp1[] = $item1->MFAC_NAME;
                }
            }
            $temp1 = implode(",",$temp1);
            $value->ACTT_MFAC_CODE_LIST = $temp1;

            /* process ACTT_MOBJ_CODE_LIST */
            $qr = "SELECT MOBJ_NAME FROM mobj WHERE MOBJ_CODE IN (".$value->ACTT_MOBJ_CODE_LIST.")";
            // order by
            $qr .= " ORDER BY MOBJ_CODE ASC";
            // load data
            $db->setQuery($qr);
            $mobj = $db->loadObjectList();

            $temp2 = array();
            if($mobj != null){
                foreach($mobj as $item2){
                    $temp2[] = $item2->MOBJ_NAME;
                }
            }
            $temp2 = implode(",",$temp2);
            $value->ACTT_MOBJ_CODE_LIST = $temp2;

            /* process ACTT_MCON_CODE_LIST */
            $qr = "SELECT MCON_NAME FROM mcon WHERE MCON_CODE IN (".$value->ACTT_MCON_CODE_LIST.")";
            // order by
            $qr .= " ORDER BY MCON_CODE ASC";
            // load data
            $db->setQuery($qr);
            $mcon = $db->loadObjectList();

            $temp3 = array();
            if($mcon != null){
                foreach($mcon as $item3){
                    $temp3[] = $item3->MCON_NAME;
                }
            }
            $temp3 = implode(",",$temp3);
            $value->ACTT_MCON_CODE_LIST = $temp3;

        }
        //Todo: xuat gia tri
        if ($result != null){
            foreach ($result as $item) {
            	foreach ($item as $key => $value) {
            	   if($key != "ACTT_PERIOD_FINISH"){
                	   //Todo: Kiểm tra hết 1 dòng để xuống hàng
                	   if($key == "ACTT_DBSY"){

                	       $csv_output .= "\"".mb_convert_encoding($value,'Shift-JIS','UTF-8')."\""; //Todo: Chuy?n d? li?u ki?u UTF-8 => Shift-JIS ?? xu?t file csv d?ng Shift-JIS
                	   }else{
        		          $csv_output .= "\"".mb_convert_encoding($value,'Shift-JIS','UTF-8')."\",";
                        }
                  }
            	}
            	$csv_output .= "\r\n";
            }
        }
        $filename = "actt_".date("Y-m-d_H-i",time());

        header("Content-Encoding: Shift-JIS");
        header("Content-Type: text/csv; charset= Shift-JIS");
        header("Content-disposition: csv" . date("Y-m-d") . ".csv");
        header("Content-disposition:attachment; filename=" . $filename . ".csv");
        print_r($csv_output);
        exit;
    }

    public function export_csv_infoleader(){
        ini_set('memory_limit', '10000M');
       //Todo: Tiêu đề dữ liệu: hiển thị dòng đầu tiên trong file CSV
        $csv_output  = "指導者システムコード,指導員コード,指導員氏名,カナ,所属センターコード,職業,性別,生年月日,登録年月日,更新年月日,有効期限年月日,抹消年月日,抹消処理年月日,郵便番号,都道府県,住所,マンション・ビル名,自宅電話番号,自宅FAX,自宅メールアドレス,携帯メールアドレス,勤務先名,所属部署,役職,郵便番号,都道府県,勤務先住所,マンション・ビル名,勤務先電話番号,勤務先FAX,勤務先メールアドレス,勤務形態,写真,人材バンク,指導者会,指導者会名,事業協力,B&G事業担当自_1,B&G事業担当至_1,B&G事業担当自_2,B&G事業担当至_2,B&G事業担当自_3,B&G事業担当至_3,B&G事業担当自_4,B&G事業担当至_4,B&G事業担当自_5,B&G事業担当至_5,B&G事業担当自_6,B&G事業担当至_6,特記事項,備考（財団記入欄）";
        $csv_output  = mb_convert_encoding($csv_output,'Shift-JIS','UTF-8');
        $csv_output .= "\n";

        /*$qr ="SELECT DISTINCT COCH_CODE,COCH_CD,ocba.OCBA_CODE,jobm.JOBM_CODE,COCH_NAME,COCH_KANA,COCH_SEX,DATE(COCH_BDATE),COCH_BUI1,COCH_POST,COCH_ADD1,COCH_TEL,COCH_FAX,COCH_MAIL,COCH_MAIF,
                      COCH_PRE1,COCH_POSW,COCH_ADD2,COCH_BUI2,COCH_PRE2,COCH_TELW,COCH_FAXW,COCH_NAMW,COCH_UNIT,COCH_POSI,COCH_MAIW,COCH_MAFW,COCH_IMG,COCH_WOKF,
                      DATE(COCH_RDATE),DATE(COCH_MDATE),DATE(COCH_LDATE),DATE(COCH_EDATE),DATE(COCH_DDATE),DATE(COCH_DATE),COCH_COMM,COCH_NOTE,COCH_FLST,COCH_CLST,DATE(COCH_BUSS1),DATE(COCH_BUSS2),DATE(COCH_BUSS3),DATE(COCH_BUSS4),DATE(COCH_BUSS5),DATE(COCH_BUSS6),
                      DATE(COCH_BUSE1),DATE(COCH_BUSE2),DATE(COCH_BUSE3),DATE(COCH_BUSE4),DATE(COCH_BUSE5),DATE(COCH_BUSE6),COCH_REGI,COCH_LDME,COCH_LKDT_CODE_LIST,COCH_HUMAN_CODE_LIST,COCH_MAIK,COCH_MAKF
                      FROM coch
                      LEFT JOIN jobm ON COCH_JOBM_CODE=JOBM_CODE
                      LEFT JOIN (coli INNER JOIN cocm ON COLI_COCM_CODE=COCM_CODE) ON COCH_CODE = COLI_COCH_CODE
                      LEFT JOIN (ocba INNER JOIN (town INNER JOIN (area INNER JOIN pref ON AREA_CODE=PREF_AREA_CODE) ON TOWN_PREF_CODE =PREF_CODE)
                                        ON OCBA_TOWN_CODE=TOWN_CODE) ON COCH_OCBA_CODE=OCBA_CODE
                      WHERE COCH_FLAG = 1
                      ORDER BY COCH_CODE DESC
                      ";*/

        $qr = "SELECT DISTINCT COCH_CODE,COCH_CD,COCH_NAME,COCH_KANA
                    ,COCH_OCBA_CODE,JOBM_NAME,COCH_SEX,DATE(COCH_BDATE)
                    ,DATE(COCH_RDATE),DATE(COCH_MDATE),DATE(COCH_LDATE),DATE(COCH_EDATE)
                    ,DATE(COCH_DDATE),COCH_POST,COCH_PRE1,COCH_ADD1
                    ,COCH_BUI1,COCH_TEL,COCH_FAX,COCH_MAIL
                    ,COCH_MAIK,COCH_NAMW,COCH_UNIT,COCH_POSI
                    ,COCH_POSW,COCH_PRE2,COCH_ADD2,COCH_BUI2
                    ,COCH_TELW,COCH_FAXW,COCH_MAIW,COCH_WOKF
                    ,COCH_IMG,COCH_HUMAN_CODE_LIST,COCH_REGI,COCH_LDME
                    ,COCH_LKDT_CODE_LIST,DATE(COCH_BUSS1),DATE(COCH_BUSE1),DATE(COCH_BUSS2)
                    ,DATE(COCH_BUSE2),DATE(COCH_BUSS3),DATE(COCH_BUSE3),DATE(COCH_BUSS4)
                    ,DATE(COCH_BUSE4),DATE(COCH_BUSS5),DATE(COCH_BUSE5),DATE(COCH_BUSS6)
                    ,DATE(COCH_BUSE6),COCH_COMM,COCH_NOTE
                    FROM coch
                    INNER JOIN jobm ON COCH_JOBM_CODE = JOBM_CODE
                    WHERE COCH_FLAG = 1
                    ORDER BY COCH_CODE DESC";
        $db = JFactory::getDbo();
		$db->setQuery($qr);
        $result = $db->loadObjectList();
        foreach($result as $value){
            if($value->COCH_SEX == 1){ $value->COCH_SEX = "男性";}else{ $value->COCH_SEX = "女性"; }
            if($value->COCH_WOKF == 1){ $value->COCH_WOKF = "常勤";}else{ $value->COCH_WOKF = "非常勤"; }
            /* process COCH_LKDT_CODE_LIST */
            $qr = "SELECT LKDT_NAME FROM lkdt WHERE LKDT_CODE IN (".$value->COCH_LKDT_CODE_LIST.")";
            // load data
            $db->setQuery($qr);
            $lkdt = $db->loadObjectList();

            $temp1 = array();
            if($lkdt != null){
                foreach($lkdt as $item1){
                    $temp1[] = $item1->LKDT_NAME;
                }
            }
            $temp1 = implode(",",$temp1);
            $value->COCH_LKDT_CODE_LIST = $temp1;

            /* process COCH_HUMAN_CODE_LIST */
            $qr = "SELECT HUMAN_NAME FROM human WHERE HUMAN_CODE IN (".$value->COCH_HUMAN_CODE_LIST.")";
            // load data
            $db->setQuery($qr);
            $human = $db->loadObjectList();

            $temp1 = array();
            if($human != null){
                foreach($human as $item1){
                    $temp1[] = $item1->HUMAN_NAME;
                }
            }
            $temp1 = implode(",",$temp1);
            $value->COCH_HUMAN_CODE_LIST = $temp1;
        }
        if ($result != null){
            foreach ($result as $item) {
            	foreach ($item as $key => $value) {
            	   //Todo: Kiểm tra hết 1 dòng để xuống hàng
            	   if($key == "COCH_NOTE"){

            	       $csv_output .= "\"".mb_convert_encoding($value,'Shift-JIS','UTF-8')."\""; //Todo: Chuyển dữ liệu kiểu UTF-8 => Shift-JIS để xuất file csv dạng Shift-JIS
            	   }else{
            		$csv_output .= "\"".mb_convert_encoding($value,'Shift-JIS','UTF-8')."\",";
                    }
            	}
            	$csv_output .= "\r\n";
            }
        }
        $filename = "info_leader_".date("Y-m-d_H-i",time());
        header("Content-Encoding: Shift-JIS");
        header("Content-Type: text/csv; charset= Shift-JIS");
        header("Content-disposition: csv" . date("Y-m-d") . ".csv");
        header("Content-disposition:attachment; filename=" . $filename . ".csv");
        print_r($csv_output);
        exit;
    }

    public function getLibUploadify(){?>
    	<script src="components/com_frmessage/public/uploadlib/jquery.min.js" type="text/javascript"></script>
        <script src="templates/toppage-BG/uploadify/jquery.uploadify.min.js" type="text/javascript"></script>
        <link rel="stylesheet" type="text/css" href="templates/toppage-BG/uploadify/uploadify.css"/>
        <style type="text/css">
            .uploadify-button {
                background-color: transparent;
                border: none;
                padding: 0;
            }
            .uploadify:hover .uploadify-button {
                background-color: transparent;
            }
            .lb_upload{
                white-space: nowrap;
            }
            /*.uploadify{
                float: left;
                margin-top: -10px;
            }
            .lb_upload{
                line-height: 15px;
                margin: 0 0 0 10px;
            }*/

        </style>
    <?php }

    public function getUpload($tenInput, $valueInput, $pathSaveUpload, $extension = '*', $sizeLimit = '26MB', $tenFile = null, $buttonText = '添付ファイルを選択してください'){ ?>
        <?php if(!$valueInput){
                $valueInput = JRequest::getVar($tenInput);
            }
        ?>
        <div id="upload">
            <input name="<?php echo $tenInput?>" value="<?php echo $valueInput?>" id="<?php echo $tenInput?>" class="hidden"/>
            <div href="#" id="file_upload_<?php echo $tenInput?>"></div>

            <label id="lb_upload_<?php echo $tenInput?>" class="lb_upload"><?php echo $valueInput?></label>

        </div>
    	<script>
            var num = 0;
            $(function() {
            	$('#file_upload_<?php echo $tenInput?>').uploadify({
                    'fileSizeLimit' : '<?php echo $sizeLimit?>',
                    'successTimeout' : 5,
                    'auto'     : true,
                    'removeCompleted' : true,
                    'multi'    : false,
                    'width'    : 200,
                    'buttonImage' : 'templates/toppage-BG/uploadify/button.png',
            		'swf'      : 'templates/toppage-BG/uploadify/uploadify.swf',
            		'uploader' : 'templates/toppage-BG/uploadify/uploadify.php?folder=<?php echo $pathSaveUpload ?>',
                    'fileTypeDesc' : 'All file',
                    'fileTypeExts' : '<?php echo $extension ?>',
                    'onUploadSuccess' : function(file, data, response) {
                        $('#<?php echo $tenInput?>').val(data);
                        $('#lb_upload_<?php echo $tenInput?>').html(data);
                    },
                    'onSelect' : function(file) {
                        num = $('.uploadify-queue-item').length;
                        $('.uploadify-queue-item').each(function(key){
                            if(key < num - 1){
                                $(this).hide();
                            }
                        });
                    }
            	});
            });
        </script>
    <?php }

    public function newline2br($str){
        $str = str_replace("\r\n", "\n", $str);
        $str = str_replace("\n", "<br>", $str);
        return $str;
    }



}
